package goodenglish.infrastructure.repository.db;

import goodenglish.domain.model.LazyList;
import goodenglish.domain.model.lexicon.Lexicon;
import goodenglish.domain.model.lexicon.LexiconRepository;
import goodenglish.domain.model.lexicon.NewWord;
import goodenglish.domain.model.lexicon.StudyInfo;
import goodenglish.domain.model.lexicon.StudyPlan;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class LexiconRepositoryDb implements LexiconRepository {
	@Override
	public List<Lexicon> findLexicons() {
		List<Lexicon> lexicons = new ArrayList<Lexicon>();
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select id, name from Lexicon;");
			while (rs.next()) {
				Lexicon lexicon = new Lexicon();
				lexicon.setId(rs.getString("id"));
				lexicon.setName(rs.getString("name"));
				lexicon.setNewWords(new LazyList<NewWord>());
				lexicon.setStudyPlans(new LazyList<StudyPlan>());
				lexicons.add(lexicon);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(stmt);
			DBUtil.closeConnection(conn);
		}
		return lexicons;
	}

	@Override
	public Lexicon getLexicon(final String id) {
		Lexicon lexicon = null;
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select id, name from Lexicon;");
			if (rs.next()) {
				lexicon = new Lexicon();
				lexicon.setId(rs.getString("id"));
				lexicon.setName(rs.getString("name"));
				lexicon.setNewWords(new LazyList<NewWord>());
				lexicon.setStudyPlans(new LazyList<StudyPlan>());
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(stmt);
			DBUtil.closeConnection(conn);
		}
		return lexicon;
	}

	@Override
	public void loadNewWords(final Lexicon lexicon) {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.getConnection();

			// 加载生词

			prep = conn
					.prepareStatement("select id,spell,explain,exampleSentStr,studyPlan_id from NewWord where lexicon_id=?;");
			prep.setString(1, lexicon.getId());
			rs = prep.executeQuery();
			List<NewWord> subtWords = new ArrayList<NewWord>();
			while (rs.next()) {
				NewWord newWord = new NewWord();
				newWord.setSpell(rs.getString("spell"));
				newWord.setId(rs.getString("id"));
				newWord.setExplain(rs.getString("explain"));
				newWord.setExampleSentStr(rs.getString("exampleSentStr"));
				if (null == rs.getString("studyPlan_id") || rs.getString("studyPlan_id").equals("")) {
					newWord.setPlaned(false);
				} else {
					newWord.setPlaned(true);
				}

				subtWords.add(newWord);
			}
			lexicon.setNewWords(subtWords);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeResultSet(rs);
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}

	}

	@Override
	public void addLexicon(final Lexicon lexicon) {
		Connection conn = null;
		PreparedStatement prep = null;
		Statement stmt = null;
		try {
			conn = DBUtil.getConnection();
			stmt = conn.createStatement();
			prep = conn.prepareStatement("insert into Lexicon(id,name) values (?,?);");
			prep.setString(1, lexicon.getId());
			prep.setString(2, lexicon.getName());
			prep.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DaoException("保存词库出错", e);
		} finally {
			DBUtil.closeStatement(stmt, prep);
			DBUtil.closeConnection(conn);
		}
		updateLexion(lexicon);
	}

	public void updateLexion(final Lexicon lexicon) {
		Connection conn = null;
		PreparedStatement prep1 = null;
		PreparedStatement prep2 = null;
		try {
			conn = DBUtil.getConnection();
			DBUtil.beginBatch(conn);
			prep2 = conn.prepareStatement("delete from NewWord where lexicon_id=?;");
			prep2.setString(1, lexicon.getId());
			prep2.executeUpdate();
			prep1 = conn
					.prepareStatement("insert into NewWord(id,spell,explain,lexicon_id,exampleSentStr) values (?,?,?,?,?);");
			for (NewWord newWord : lexicon.getNewWords()) {
				prep1.setString(1, newWord.getId());
				prep1.setString(2, newWord.getSpell());
				prep1.setString(3, newWord.getExplain());
				prep1.setString(4, lexicon.getId());
				prep1.setString(5, newWord.getExampleSentStr());
				prep1.addBatch();
			}

			prep1.executeBatch();
			DBUtil.commitBatch(conn);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new DaoException("保存词库单词出错", e);
		} finally {
			DBUtil.closeStatement(prep1, prep2);
			DBUtil.closeConnection(conn);
		}
	}

	@Override
	public void loadStudyPlans(final Lexicon lexicon) {
		List<StudyPlan> studyPlans = new ArrayList<StudyPlan>();
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs1;
		try {

			conn = DBUtil.getConnection();
			prep = conn.prepareStatement("select id, name from StudyPlan where lexicon_id=?;");
			prep.setString(1, lexicon.getId());
			rs1 = prep.executeQuery();
			while (rs1.next()) {
				StudyPlan studyPlan = new StudyPlan();
				studyPlan = new StudyPlan();
				studyPlan.setId(rs1.getString("id"));
				studyPlan.setName(rs1.getString("name"));
				studyPlan.setStudyInfos(new LazyList<StudyInfo>());
				studyPlans.add(studyPlan);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}
		lexicon.setStudyPlans(studyPlans);
	}

	public void delNewWord(String id) {
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DBUtil.getConnection();

			prep = conn.prepareStatement("delete from NewWord where id=?");
			prep.setString(1, id);
			prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("删除生词出错,id:" + id, e);
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}

	}

	public void editNewWord(NewWord newWord) {
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DBUtil.getConnection();
			prep = conn.prepareStatement("update NewWord set spell=?,explain=?,exampleSentStr=? where id=?");
			prep.setString(1, newWord.getSpell());
			prep.setString(2, newWord.getExplain());
			prep.setString(3, newWord.getExampleSentStr());
			prep.setString(4, newWord.getId());
			prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("修改生词出错，id:" + newWord.getId(), e);
		} finally {
			DBUtil.closeStatement(prep);
			DBUtil.closeConnection(conn);
		}

	}
}
